﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;

namespace GZ.Entity
{
    /// <summary>
    /// 客户实体类
    /// </summary>
    public class ECustomer
    {
        public ECustomer()
        {
            this.address = string.Empty;
            this.businessdistrictid = 0;
            this.customername = string.Empty;
            this.email = string.Empty;
            this.floorid = 0;
            this.isdelete = 0;
            this.memo = string.Empty;
            this.officeid = 0;
            this.pwd = string.Empty;
            this.regionid = 0;
            this.telnum = string.Empty;
            this.address = string.Empty;
        }

        #region 属性
        private int id;

        public int Id
        {
            get { return id; }
            set { id = value; }
        }
        private string customername;

        public string CustomerName
        {
            get { return customername; }
            set { customername = value; }
        }
        private string email;

        public string Email
        {
            get { return email; }
            set { email = value; }
        }
        private string pwd;

        public string Pwd
        {
            get { return pwd; }
            set { pwd = value; }
        }
        private string telnum;

        public string TelNum
        {
            get { return telnum; }
            set { telnum = value; }
        }
        private int regionid;

        public int RegionId
        {
            get { return regionid; }
            set { regionid = value; }
        }

        private int businessdistrictid;

        public int BusinessDistrictId
        {
            get { return businessdistrictid; }
            set { businessdistrictid = value; }
        }
        private int officeid;

        public int OfficeId
        {
            get { return officeid; }
            set { officeid = value; }
        }
        private int floorid;

        public int FloorId
        {
            get { return floorid; }
            set { floorid = value; }
        }
        private string address;

        public string Address
        {
            get { return address; }
            set { address = value; }
        }
        private string memo;

        public string Memo
        {
            get { return memo; }
            set { memo = value; }
        }
        private int isdelete;

        public int IsDelete
        {
            get { return isdelete; }
            set { isdelete = value; }
        }
        #endregion

        #region

        /// <summary>
        /// 插入客户方法
        /// </summary>
        /// <param name="obj">客户实体</param>
        /// <returns>返回新添加的客户Id，0为添加失败</returns>
        public int Insert(ECustomer obj)
        {
            string sql = @"INSERT INTO  tb_customer(
                            CustomerName,Email,
                            PWD,TelNum,RegionId,
                            BusinessDistrictId,OfficeId,FloorId,
                            Memo,IsDelete,Address)
                           VALUES
                            (@CustomerName,@Email,
                             @PWD,@TelNum,@RegionId,
                             @BusinessDistrictId,@OfficeId,@FloorId,
                             @Memo,@IsDelete,@Address
                            );";
            MySqlParameter[] parameters = new MySqlParameter[11];
            parameters[0] = new MySqlParameter("@CustomerName", obj.CustomerName);
            parameters[1] = new MySqlParameter("@Email", obj.Email);
            parameters[2] = new MySqlParameter("@PWD", obj.Pwd);
            parameters[3] = new MySqlParameter("@TelNum", obj.TelNum);
            parameters[4] = new MySqlParameter("@RegionId", obj.RegionId);
            parameters[5] = new MySqlParameter("@BusinessDistrictId", obj.BusinessDistrictId);
            parameters[6] = new MySqlParameter("@OfficeId", obj.OfficeId);
            parameters[7] = new MySqlParameter("@FloorId", obj.FloorId);
            parameters[8] = new MySqlParameter("@Memo", obj.Memo);
            parameters[9] = new MySqlParameter("@IsDelete", obj.IsDelete);
            parameters[10] = new MySqlParameter("@Address", obj.Address);
            MySqlHelper.ExecuteNonQuery(GZ.DAL.MySQLHelper.connStr, sql, parameters);
            sql = "SELECT Id FROM tb_customer WHERE Email=@Email AND IsDelete=0";
            parameters = new MySqlParameter[1];
            parameters[0] = new MySqlParameter("@Email", obj.Email);
            DataTable dt = MySqlHelper.ExecuteDataset(GZ.DAL.MySQLHelper.connStr, sql, parameters).Tables[0];
            if (dt.Rows.Count > 0)
            {
                return Convert.ToInt32(dt.Rows[0]["Id"]);
            }
            else
            {
                return obj.Id;
            }
        }

        /// <summary>
        /// 根据邮箱验证用户是否已存在(包括已删除客户)
        /// </summary>
        /// <remarks>
        /// 查询数据库内所有的客户记录，确认是否存在指定的Email地址。该字段数据库实现了唯一约束。
        /// </remarks>
        /// <param name="Email">电子邮箱地址</param>
        /// <returns>存在返回True，不存在返回False</returns>
        public bool IsExistCustomerByEmail(string Email)
        {
            string sql = "SELECT Id FROM tb_customer WHERE Email=@Email";
            MySqlParameter[] parameters = new MySqlParameter[1];
            parameters[0] = new MySqlParameter("@Email", Email);
            DataTable dt = MySqlHelper.ExecuteDataset(GZ.DAL.MySQLHelper.connStr, sql, parameters).Tables[0];
            if (dt.Rows.Count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 根据邮箱查询是否客户
        /// </summary>
        /// <remarks>
        /// 查询数据库内所有的客户记录，确认是否存在指定的Email地址 
        /// </remarks>
        /// <param name="Email">电子邮箱地址</param>
        /// <returns>返回客户列表</returns>
        public DataTable GetCustomerByEmail(string Email)
        {
            string sql = "SELECT * FROM tb_customer WHERE Email=@Email";
            MySqlParameter[] parameters = new MySqlParameter[1];
            parameters[0] = new MySqlParameter("@Email", Email);
            DataTable dt = MySqlHelper.ExecuteDataset(GZ.DAL.MySQLHelper.connStr, sql, parameters).Tables[0];
            return dt;
        }

        /// <summary>
        /// 根据电话查询是否客户
        /// </summary>
        /// <remarks>
        /// 查询数据库内所有的客户记录，确认是否存在指定的电话
        /// </remarks>
        /// <param name="Email">电话</param>
        /// <returns>返回客户列表</returns>
        public DataTable GetCustomerByTel(string Tel)
        {
            string sql = "SELECT * FROM tb_customer WHERE TelNum=@TelNum";
            MySqlParameter[] parameters = new MySqlParameter[1];
            parameters[0] = new MySqlParameter("@TelNum", Tel);
            DataTable dt = MySqlHelper.ExecuteDataset(GZ.DAL.MySQLHelper.connStr, sql, parameters).Tables[0];
            return dt;
        }

        /// <summary>
        /// 根据邮箱密码查询（用于登录使用）
        /// </summary>
        /// <returns>客户列表</returns>
        public DataTable GetCustomerByEmailPwd(string Email, string Pwd)
        {
            string sql = "SELECT * FROM tb_customer WHERE Email=@Email AND PWD=@PWD AND IsDelete=0 ";
            MySqlParameter[] parameters = new MySqlParameter[2];
            parameters[0] = new MySqlParameter("@Email", Email);
            parameters[1] = new MySqlParameter("@PWD", Pwd);
            return MySqlHelper.ExecuteDataset(GZ.DAL.MySQLHelper.connStr, sql, parameters).Tables[0];
        }

        /// <summary>
        /// 根据客户Id查询非删除客户
        /// </summary>
        /// <param name="CustomerId">客户Id</param>
        /// <returns>客户对象，未找到返回null</returns>
        public ECustomer GetCustomerById(int CustomerId)
        {
            string sql = "SELECT * FROM tb_customer WHERE Id=@Id AND IsDelete=0 ";
            MySqlParameter[] parameters = new MySqlParameter[1];
            parameters[0] = new MySqlParameter("@Id", CustomerId);
            DataTable dt = MySqlHelper.ExecuteDataset(GZ.DAL.MySQLHelper.connStr, sql, parameters).Tables[0];
            IList<ECustomer> list = GZ.Common.ModelConvertHelper<ECustomer>.ConvertToModel(dt);
            if (list.Count > 0)
            {
                return list[0];
            }
            return null;
        }


        /// <summary>
        /// 更新客户对象
        /// </summary>
        /// <param name="obj">客户对象</param>
        /// <returns>True为成功，False为失败</returns>
        public bool Update(ECustomer obj)
        {
            string sql = @"UPDATE  tb_customer SET CustomerName = @CustomerName,Email = @Email,PWD = @PWD,
                            TelNum = @TelNum,RegionId = @RegionId,BusinessDistrictId = @BusinessDistrictId,
                            OfficeId = @OfficeId,FloorId = @FloorId,Memo = @Memo,IsDelete = @IsDelete,
                            Address = @Address  WHERE Id=@Id;";
            MySqlParameter[] parameters = new MySqlParameter[12];
            parameters[0] = new MySqlParameter("@CustomerName", obj.CustomerName);
            parameters[1] = new MySqlParameter("@Email", obj.Email);
            parameters[2] = new MySqlParameter("@PWD", obj.Pwd);
            parameters[3] = new MySqlParameter("@TelNum", obj.TelNum);
            parameters[4] = new MySqlParameter("@RegionId", obj.RegionId);
            parameters[5] = new MySqlParameter("@BusinessDistrictId", obj.BusinessDistrictId);
            parameters[6] = new MySqlParameter("@OfficeId", obj.OfficeId);
            parameters[7] = new MySqlParameter("@FloorId", obj.FloorId);
            parameters[8] = new MySqlParameter("@Memo", obj.Memo);
            parameters[9] = new MySqlParameter("@IsDelete", obj.IsDelete);
            parameters[10] = new MySqlParameter("@Address", obj.Address);
            parameters[11] = new MySqlParameter("@Id", obj.Id);
            int val = MySqlHelper.ExecuteNonQuery(GZ.DAL.MySQLHelper.connStr, sql, parameters);
            return val > 0 ? true : false;
        }

        #endregion
    }
}
